import sys
import MySQL

class Person:
    def __init__(self, PID, f_Name, l_Name, UserName, Admin = 'False'):
        self.PID = PID
        self.f_Name = f_Name
        self.l_Name = l_Name
        self.UserName = UserName
        self.Admin = bool('True' == Admin)

class People:
    def __init__(self, SQLConn, dBName = "People_data"):
        self.People = {}        
        self.SQLConn = SQLConn
        
    def AddPerson(self, First_Name, Last_Name, UserName):
        PID = 1
        self.SQLConn.SendCommand("""
         INSERT INTO People_data (f_name, l_name, Usr_name)
         VALUES ('%s', '%s', '%s')
       """ %(First_Name, Last_Name, UserName))
        
    
    def RemovePerson(self, PID):
        self.SQLConn.SendCommand("""    
         DELETE FROM People_data where Peo_id=%d
         """ % PID)
        
    def GetAllPIDs(self):
        self.SQLConn.SendCommand("SELECT Peo_id FROM People_data")
        rows = self.SQLConn.FetchAll ()
        PIDs = []
        for pid in rows:
            PIDs.append(pid[0])
        return PIDs
        
        
    def GetAllPeople(self):
        self.SQLConn.SendCommand("SELECT Peo_id, f_name, l_name, Usr_name, Admin FROM People_data")
        rows = self.SQLConn.FetchAll ()
        People = []
        for person in rows:
            People.append(Person(int(person[0]), person[1], person[2], person[3], person[4]))
        return People
    
    def GetAllPeopleDict(self):
        self.SQLConn.SendCommand("SELECT Peo_id, f_name, l_name, Usr_name, Admin FROM People_data")
        rows = self.SQLConn.FetchAll ()
        People = {}
        for person in rows:
            People[int(person[0])] = Person(int(person[0]), person[1], person[2], person[3], person[4])
        return People
    
    
    def GetPerson(self, f_name='', l_name = '', PID = -1, UserName = '', Admin = ''):
        First = True
        str = """WHERE """
        if Admin != '':
            if First:
                First = False
                str = str + """Admin = '%s' """ % Admin
            else:
                str = str + """AND Admin = '%s' """ % Admin
        if PID != -1:
            if First:
                First = False
                str = str + """Peo_id = %d """ % PID
            else:
                str = str + """AND Peo_id = '%d' """ % PID
        if f_name != '':
            if First:
                First = False
                str = str + """f_name = '%s' """ % f_name
            else:
                str = str + """AND f_name = '%s' """ % f_name
        if l_name != '':
            if First:
                First = False
                str = str + """l_name = '%s' """ % l_name
            else:
                str = str + """AND l_name = '%s' """ % l_name
        if UserName != '':
            if First:
                First = False
                str = str + """Usr_name = '%s' """ % UserName
            else:
                str = str + """AND Usr_name = '%s' """ % UserName
        
        self.SQLConn.SendCommand("""SELECT Peo_id, f_name, l_name, Usr_name, Admin FROM People_data """ + str)
        Data = self.SQLConn.FetchAll ()
        if len(Data) >  1:
            Res = []
            for person in Data:
                Res.append(Person(int(person[0]), person[1], person[2], person[3], person[4]))
            
        elif len(Data) == 1:
            person = Data[0]
            Res = Person(int(person[0]), person[1], person[2], person[3], person[4])
        else:
            Res = None
            
        return Res
    
    def UpdatePerson(self, Person):
       self.SQLConn.SendCommand("""UPDATE People_data
                                SET f_name = '%s',
                                l_name = '%s',
                                Usr_name = '%s',
                                Admin = '%s'
                                WHERE Peo_id = %d
                                """ % (Person.f_Name, Person.l_Name, Person.UserName, Person.Admin, Person.PID))
       
    
if __name__ == '__main__':
    SQL = MySQL.Connection()
    
        
    
    #SQL.SendCommand(""" ALTER TABLE People_data ADD Admin VARCHAR(60) Default 'False'""")
    #cursor.execute("""CREATE TABLE People_data(
    #                Peo_id int unsigned not null auto_increment primary key,
    #                f_name varchar(20),
    #                l_name varchar(20))
    #                """)



    P = People(SQL)
    exit = False
    while not exit:
        Input = raw_input("Now What? : ")
        if Input == 'exit':
            exit = True
        if Input == 'new':
            first = raw_input("First Name : ")
            second = raw_input("Last Name : ")
            User = raw_input("UserName :")
            P.AddPerson(first, second, User)
            
        if Input == 'del':
            PID = int(raw_input("Number : "))
            P.RemovePerson(PID)
            
        if Input == 'print':
            Pep =  P.GetAllPeople()
            for w in Pep:
                print w.PID, w.f_Name
                
        if Input == 'Update':
            ID = int(raw_input("PID : "))
            Person = P.GetPerson(PID = ID)
            print Person.f_Name
            Person.f_Name = 'Test123'
            P.UpdatePerson(Person)
            
        if Input == 'Get':
            Name = raw_input("Name : ")
            l_name = raw_input("LName : ")
            P.GetPerson(Name, l_name)
            
    print "Finished"
      
    SQL.close()  
    
    
    
  
    
        